package com.pub.database;

import java.sql.*;
import java.util.*;

//Connection pool Use
import javax.naming.*;
import javax.sql.*;

import com.pub.tools.*;
import com.pub.encryption.PublicEncryption;
import com.pub.web.WebFunction;

/**
 *
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2004</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 *
 */

/**
 *
 *  目的：查詢SQL的資料放入二維陣列中，或執行一個SQL語法<br>
 *  方法：hashtableSQL (傳入Select SQL語法，傳回Hashtable) ==>欄位元素可直接用別名，易於維護，建議改用這個方法<br>
 *     querySQL (傳入Select SQL語法，傳回ArrayList) ==>因陣列元素不易識別，因此建議改用 hashtableSQL 方法<br>
 *     selectData (傳入Select SQL語法，傳回data二維陣列) --建議不要再用了(會耗記憶體)<br>
 *     executeSQL (執行Insert,Update,Delete SQL語法，成功傳回true,失敗傳回false)<br><br>
 *  範例：<br><br>
 *    方法：Hashtable hashtableSQL(strSql) ==> 查無資料會回傳null  (強烈建議使用這個方法)<br><br>
 * 在Action中<br><br>
 *   Hashtable hstTemp = new QueryBean().hashtableSQL("Select button_no from wkf_button")<br><br>
 *   km_attachForm.setHstAttach(hstTemp);  //將回傳的Hashtable設定到km_attachForm<br><br>
 * 在ActionForm中<br><br>
 *   private Hashtable hstAttach=null;  //用來將Km_attachAction中查詢到Hashtable傳回首頁<br><br>
 *   public Hashtable getHstAttach() {  //Struts自動抓取到showMenu中<br><br>
 *    return hstAttach;<br><br>
 *   }<br><br>
 *   public void setHstAttach(Hashtable hstAttach) {<br><br>
 *  this.hstAttach = hstAttach;<br><br>
 *   }<br><br>
 * 在jsp中<br><br>
 * <%@ page contentType="text/html; charset=Big5" import="java.util.Hashtable" %><br><br>
 *    <jsp:useBean id="km_attachForm" scope="request" class="com.alpha.km.actionform.Km_attachForm" /><br><br>
 *    //從km_attachForm將Hashtable取出<br><br>
 *    Hashtable hstAttach = km_attachForm.getHstAttach();<br><br>
 *    if (hstAttach!=null) {  //此行要記得判斷<br><br>
 *       for (int i=0;i<hstAttach.size();i++) //第一筆資料會放在 "0" 的Hashtable物件中，以此類推<br><br>
 *      out.println(((Hashtable)hstAttach.get(String.valueOf(i))).get("BUTTON_NO").toString()); //記得欄位的別名要用大寫<br><br>
 *    }<br><br>
 *<br><br>
 * 方法：ArrayList querySQL(strSql) ==>由於用陣列值0,1,2，不好辯識欄位名稱，所以建議改採 hashtableSQL 方便日後維護<br><br>
 *   在Action中<br><br>
 * String strSql ="select column1 columnLabel1,column2 columnLabel2 from table";<br><br>
 *    QueryBean objBean = new QueryBean();<br><br>
 *    ArrayList arlAttach = objBean.querySQL(strSql); //從objBean將AryList取出<br><br>
 *    km_attachForm.setArlAttach(arlAttach);  //將回傳的ArrayList設定到km_attachForm<br><br>
 *   在ActionForm中<br><br>
 *     private ArrayList arlAttach=null;  //用來將Km_attachAction中查詢到ArrayList傳回首頁<br><br>
 *     public ArrayList getArlAttach() {  //Struts自動抓取到showMenu中<br><br>
 *       return arlAttach;<br><br>
 *     }<br><br>
 *     public void setArlAttach(ArrayList arlAttach) {<br><br>
 *       this.arlAttach = arlAttach;<br><br>
 *     }<br><br>
 *   在jsp中<br><br>
 * <%@ page contentType="text/html; charset=Big5" import="java.util.ArrayList" %><br><br>
 *    <jsp:useBean id="km_attachForm" scope="request" class="com.alpha.km.actionform.Km_attachForm" /><br><br>
 * /從km_attachForm將AryList取出<br><br>
 *    //(COL0=FLOW_NUM,COL1=ID,COL2=FILENAME,COL3=SIZE) ==>此行從伺服器的訊息貼過來，以方便日後維護<br><br>
 *    ArrayList arlAttach = km_attachForm.getArlAttach();<br><br>
 *    ArrayList arlTemp = new ArrayList();<br><br>
 *    if (arlAttach!=null) {<br><br>
 *       for(int i=0;i<arlAttach.size();i++){<br><br>
 *           arlTemp=(ArrayList) arlAttach.get(i);<br><br>
 *           for (int j=0;j<arlTemp.size();j++)<br><br>
 *               out.println(" " + arlTemp.get(j)); //將每個欄位值列印出來<br><br>
 *       }<br><br>
 *    }<br><br>
 *<br><br>
 *   方法：void selectData(strSql) ==>由於會耗記憶體，所以建議不要使用了<br><br>
 *   在Action中<br><br>
 * String strSql ="select column1 columnLabel1,column2 columnLabel2 from table";<br><br>
 *    QueryBean obj = new QueryBean();<br><br>
 *    qb.selectData(strSql);<br><br>
 * ActionForm.setRowcount(qb.getRowcount());<br><br>
 *   在jsp中利用for回圈<br><br>
 * <jsp:useBean id="mem" scope="session" class="com.alpha.km.share.QueryBean" /><br><br>
 *    取各欄資料<%=mem.getData()[i][1]%><br><br>
 * 取各欄欄名<%=mem.getColumnLabel[i]%><br><br>
 * 取得資料筆數<%int length=ActionForm.getRowcount();%><br><br>
 *<br><br>
 *   方法：executeSQL(String strSql)<br><br>
 *    String strSql = "Insert into KM_ADD_DOC(FLOW_CODE,FLOW_NUM,TYPE_ID) values ('111','test','test')";<br><br>
 *    QueryBean obj = new QueryBean(); //呼叫公用的Bean,執行 SQL<br><br>
 *    boolean blnReturn=obj.executeSQL(strSql); //執行SQL ,成功傳回true,失敗傳回false<br><br>
 *<br><br>
 *   方法：executeSQL(String strSql,Vector vtrParams)<br><br>
 *    String strSql = "Insert into KM_ADD_DOC (FLOW_CODE,FLOW_NUM,TYPE_ID) values (?,?,?)";<br><br>
 *    com.alpha.km.share.QueryBean obj = new com.alpha.km.share.QueryBean(); //呼叫公用的Bean,執行SQL語法<br><br>
 *    java.util.Vector vtrData = new java.util.Vector();<br><br>
 *    vtrData.add("222");<br><br>
 *    vtrData.add("5555555");<br><br>
 *    vtrData.add("test2");<br><br>
 * boolean blnReturn=obj.executeSQL(strSql,vtrData); //執行SQL,成功傳回true,失敗傳回false<br><br>
 *
 */

public class QueryBean {
    private Connection gconn = null;
    private String gstrDBDriver = null;
    private String gstrDBPrefix = null;
    private String gstrActiveSite = null;
    private boolean gblnDBDebug = false;
    private String gstrLookupName = null;
    private String gstrSourceCharset = null;
    private String gstrTargetCharset = null;
    private String gstrDBType = null;
    private String gstrDBHost = null;
    private String gstrDBPort = null;
    private String gstrDBSid = null;
    private String gstrDBUser = null;
    private String gstrDBPasswords = null;
    private String gstrEncryption = null;
    private String gstrDBAlias = null;
    private boolean gblnJustJDBC = false;

    private String gstrProgName; //該支程式的名稱
    private int gintRowcount = 0; //資料筆數
    private boolean gblnConPool = false; //不須修改
    private String gstrErrMsg; //用來回傳錯誤訊息用
    private ArrayList<String> garlColumnLabel = null; //儲存rs的欄位名稱 (ArrayList)

    /*private QueryBean() {
        try {
            setProgName(PublicFunction.getObjectName(this)); //設定該支程式的名稱
            if (setBundleValue()) {
                createConnection();
            }
        } catch (Exception ex) {
            setErrMsg(getProgName() + " (QueryBean) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() + " (QueryBean) --> Exception : " +
                               ex.toString());
        }
    }*/

    /**
     * 傳入已將DB_Config讀入ResourceBundle物件
     * @param prbdDBConfig ResourceBundle

    private QueryBean(ResourceBundle prbdDBConfig) {
        try {
            setProgName(PublicFunction.getObjectName(this)); //設定該支程式的名稱
            if (setBundleValue(prbdDBConfig)) {
                createConnection();
            }
        } catch (Exception ex) {
            setErrMsg(getProgName() + " (QueryBean) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() + " (QueryBean) --> Exception : " +
                               ex.toString());
        }
    }*/

    /**
     * 傳入已將DB_Config讀入PropertyBean物件
     * @param pptyProperty PropertyBean

    private QueryBean(PropertyBean pptyProperty) {
        try {
            setProgName(PublicFunction.getObjectName(this)); //設定該支程式的名稱
            if (setBundleValue(pptyProperty)) {
                createConnection();
            }
        } catch (Exception ex) {
            setErrMsg(getProgName() +
                      " (QueryBean(pptyProperty)) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() +
                               " (QueryBean(pptyProperty)) --> Exception : " +
                               ex.toString());
        }
    }*/

    public QueryBean(String pstrDBAlias, boolean pblnDBDebug,
                     String pstrSourceCharset, String pstrTargetCharset) {
        setProgName(PublicFunction.getObjectName(this)); //設定該支程式的名稱
        setDBAlias(pstrDBAlias);
        setDBDebugMode(pblnDBDebug);
        setSourceCharset(pstrSourceCharset);
        if (pstrTargetCharset != null && pstrTargetCharset.equals("jdbc")) {
            setTargetCharset(pstrSourceCharset);
            gblnJustJDBC = true;
        } else {
            setTargetCharset(pstrTargetCharset);
        }
        setLookupName(pstrDBAlias);

        try {
            if (setBundleValue(pstrDBAlias)) {
                createConnection();
            }
        } catch (Exception ex) {
            setErrMsg(getProgName() + " (QueryBean(pstrDBAlias,pblnDBDebug,pstrSourceCharset,pstrTargetCharset)) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() + " (QueryBean(pstrDBAlias,pblnDBDebug,pstrSourceCharset,pstrTargetCharset)) --> Exception : " +
                               ex.toString());
        }
    }

    private void createConnection() throws Exception {
        String lstrDBLocal = null;
        String lstrDBPrefix = null;

        try {
            if (getDBDebugMode()) {
                System.out.println(
                        "\n*** " +
                        PublicFunction.getSYSDATE("yyyy/MM/dd HH:mm:ss") +
                        " Start new Connection ***");
            }

            if (!gblnJustJDBC) { //這是當想強制用jdbc做測試時所留的後門
                setConnection(getConnPool());
            }

            if (getConnection() != null) {
                setUseConnPool(true);
                if (getDBDebugMode()) {
                    System.out.println(
                            "\n*** Use Connection pool Connection! ***");
                }
            } else {
                /* 這是透過ODBC 連接 ACCESS DB 的語法(注意!!stmt init 時多了兩個參數 第一個參數代表可以前後移動 第二個代表ResultSet可否直接update資料)
                                 String url = "jdbc:odbc:swdmdb";
                                 DriverManager.registerDriver((Driver) (Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance()));

                 Connection conn = DriverManager.getConnection(url,"","");
                                 Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);*/

                setUseConnPool(false);

                lstrDBPrefix = getDBPrefix() + getDBLocation(getDBType().toUpperCase(),getDBHost().toLowerCase().trim(),getDBPort().trim(),getDBSid().trim());

                DriverManager.registerDriver((Driver) (Class.forName(
                        getDBDriver()).newInstance()));
                setConnection(DriverManager.getConnection(lstrDBPrefix,
                        getDBUser(), getDBPasswords()));

                if (getDBDebugMode()) {
                    System.out.println("\n*** Use JDBC Connection! ***");
                }
            }

            if (getDBDebugMode()) {
                System.out.println("\n*** " +
                                   PublicFunction.getSYSDATE("yyyy/MM/dd HH:mm:ss") + " Connection 不再使用時，請務必透過QueryBean.close()，QueryBean 會幫您將Connection rollback後再close並set null ***");
            }
        } catch (Exception e) {
            System.out.println("\n*** QueryBean.java (createConnection) --> 資料庫連線失敗 Database Connect Fail , 請檢查 DB_Config.properties 下列設定值是否正確! ***" +
                               "\n" + getDBAlias() + "_DBHost=" +
                               getDBHost().toLowerCase() +
                               "\n" + getDBAlias() + "_DBPort=" + getDBPort() +
                               "\n" + getDBAlias() + "_DBSid=" + getDBSid() +
                               "\n" + getDBAlias() + "_DBUser=" + getDBUser() +
                               "\n" + getDBAlias() + "_DBPwd=" + getDBPasswords() +
                               "\n\n" +
                               "\n" + getDBType() + "_DBDriver=" + getDBDriver() +
                               "\nDB_Prefix=" + lstrDBPrefix +
                               "\n*** End ***");
            setErrMsg(getProgName() + "(createConnection) Exception:" +
                      e.toString());
            throw new Exception("QueryBean create connection fail!! " +
                                e.toString());
        }
    }

    /* 取得DBBean的實體 */
    private DBBean ConnectionDB(Connection pconn) {
        DBBean db = null;
        try {
            db = new DBBean(pconn, getDBType());
        } catch (Exception ex) {
            setErrMsg(getProgName() + " (ConnectionDB) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() +
                               " (ConnectionDB) --> Exception : " +
                               ex.toString());
        }
        return db;
    }

    /*取得Tomcat Connection pool的連線 */
    private Connection getConnPool() {
        Connection conn = null;
        try {
            Context ctx = new InitialContext();
            Context envContext = (Context) ctx.lookup("java:/comp/env");
            DataSource ds = (DataSource) envContext.lookup(getLookupName());
            conn = ds.getConnection();
        } catch (Exception ex) {
            //ex.printStackTrace();
            //System.out.println(this.toString().substring(this.toString().lastIndexOf('.') + 1,this.toString().lastIndexOf('@')) + ".java" +  " (getConn) Exception --> " + ex.toString());
        }
        return conn;
    } //End-getConn

    /**
     * 傳入一個SQL語法，有資料回傳一個ArrayList，無資料回傳null
     * @param strSql String SQL 語法
     * @return Hashtable 二維ArrayList，第一維是row num ，第二維是select field seqence
     * @sample java.util.ArrayList arlTemp = new QueryBean().querySQL("Select button_no from wkf_button");<br>
     * if (arlTemp!=null) {  //此行要記得判斷<br>
     *   for (int i=0;i<arlTemp.size();i++) //第一筆資料會放在 "0" 的Hashtable物件中，以此類推<br>
     *     System.out.println(((ArrayList)arlTemp.get(i)).get(0).toString()); //記得欄位的別名要用大寫<br>
     *   }<br>
     * }
     */
    public ArrayList<ArrayList<Object>> querySQL(String strSql) {
        ArrayList<ArrayList<Object>> arlDataTmp = null; //回傳用的ArrayList
        if (getDBDebugMode()) {
            System.out.println(getProgName() + " (querySQL) --> Start time =" +
                               PublicFunction.getSYSDATE("yyyy/MM/dd HH:mm:ss"));
            System.out.println(getProgName() + " (querySQL) --> strSql=" +
                               strSql);
        }
        //連結資料庫
        DBBean db = ConnectionDB(getConnection());
        //-------------------

        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        ArrayList<Object> arlTemp = new ArrayList<Object>();
        Object lobjData = null;

        try {
            // rs = db.querySQL(strSql);  //hws0424
            rs = db.querySQL(convertSQLParams(strSql, getSourceCharset(),
                                              getTargetCharset()));
            //取得中繼資料(MetaData)，藉由它取得查詢結果的相關資訊。
            rsmd = rs.getMetaData();
            //取得欄位數。
            int numcols = rsmd.getColumnCount();
            //取得欄位名稱
            garlColumnLabel = new ArrayList<String>(); //這邊要小心，不可再重覆宣告
            for (int i = 0; i < numcols; i++) {
                garlColumnLabel.add(String.valueOf(rsmd.getColumnLabel(i + 1)));
            }
            //把ResultSet資料放到ArrayList中
            int rowCountTmp = 0;
            String strColType = "", strTemp = "";

            //rs = db.querySQL(strSql);
            while (rs.next()) {
                if (arlDataTmp == null) {
                    arlDataTmp = new ArrayList<ArrayList<Object>>();
                }
                for (int j = 0; j < numcols; j++) {
                    strColType = (String) rsmd.getColumnTypeName(j + 1);
                    // if (getDBDebugMode()) System.out.println(getProgName() + " (querySQL) --> colLabel=" + rsmd.getColumnLabel(j + 1) + " data="+ rs.getObject(j+1) + "  colType=" + strColType);
                    lobjData = rs.getObject(j + 1);
                    if (lobjData == null) {
                        if (strColType.equalsIgnoreCase("NUMBER") ||
                            strColType.equalsIgnoreCase("FLOAT") ||
                            strColType.equalsIgnoreCase("LONG") ||
                            strColType.substring(0, 3).equalsIgnoreCase("DEC")) {
                            strTemp = "0";
                        } else {
                            strTemp = "";
                        }
                    } else {
                        strTemp = WebFunction.replaceReserveCharOnWebPage(
                                String.valueOf(lobjData.toString()).trim());
                    }
                    // if (getDBDebugMode()) System.out.println(getProgName() + " (querySQL) --> strTemp=" + strTemp);
                    arlTemp.add(strTemp);
                    lobjData = null;
                    
                    if(getDBDebugMode()){//將各欄位名稱列印出來看
                        if(rowCountTmp==0){
                            if(j==0){System.out.print("QueryBean.java (querySQL) --> ");}
                            System.out.print((j == 0 ? "" : ",") + "COL" +
                                         String.valueOf(j) + "=" +
                                         String.valueOf(rsmd.getColumnLabel(j + 1)) +
                                         (j == numcols - 1 ? "\n" : ""));
                        }
                    }
                }
                arlDataTmp.add(new ArrayList<Object>(arlTemp));
                arlTemp.clear();
                rowCountTmp++;
            } //end - while

            if(getDBDebugMode()){
                System.out.println(getProgName() + " (querySQL) --> rowcount=" +
                   rowCountTmp + " numcols=" + numcols);
                System.out.print(getProgName() + " (querySQL) --> ");
            }

            setRowcount(rowCountTmp); //將筆數存入記憶體
            
        } catch (Exception ex) {
            setErrMsg(getProgName() + " (querySQL) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() +
                               " (executeSQL,parameter:strSql) --> SQL : " +
                               strSql);
            System.out.println(getProgName() + " (querySQL) --> Exception : " +
                               ex.toString());
        } finally {
            if (getDBDebugMode()) {
                System.out.println(getProgName() +
                                   " (querySQL) --> Return Value " +
                                   (arlDataTmp == null ? "is null" : "not null"));
                System.out.println(getProgName() + " (querySQL) --> end time =" +
                                   PublicFunction.getSYSDATE(
                        "yyyy/MM/dd HH:mm:ss"));
            }
            //---------------------------
            //釋放沒用的記憶體空間
            if (lobjData != null) {
                lobjData = null;
            }
            if (arlTemp != null) {
                arlTemp = null; //釋放沒用的ArrayList
            }
            if (rsmd != null) {
                rsmd = null; //釋放沒用的ResultSetMetaData
            }
            if (rs != null) {
                try {
                    if (!getDBType().toUpperCase().trim().equals("ACCESS") && 
                    	!getDBType().toUpperCase().trim().equals("SQLITE")) {
                        rs.getStatement().close();
                        rs.close();
                        rs = null;
                    }
                } catch (Exception se) {
                                        if(getDBDebugMode()){//因若使用connection pool resultset close 一定會有exception ，所以用debug 來決定要不要秀出這個exception
                                            se.printStackTrace();
                                        }
                }
                rs = null;
            } //釋放沒用的ResultSet
            //----------------------------
            if (db != null) {
                db.close();
            }
        }
        return arlDataTmp;
    } //querySQL -- End

    /**
     * 傳入一個SQL語法，有資料回傳一個Hashtable，無資料回傳null
     * @param strSql String SQL 語法
     * @return Hashtable 二維Hashtable，第一維的key的row num ，第二維的key是field name(or alias name)
     * @sample java.util.Hashtable hstTemp = new QueryBean().hashtableSQL("Select button_no from wkf_button");<br>
     * if (hstTemp!=null) {  //此行要記得判斷<br>
     *   for (int i=0;i<hstTemp.size();i++) //第一筆資料會放在 "0" 的Hashtable物件中，以此類推<br>
     *     System.out.println(((Hashtable)hstTemp.get(String.valueOf(i))).get("BUTTON_NO").toString()); //記得欄位的別名要用大寫<br>
     *   }<br>
     * }
     */
    public Hashtable<String,Hashtable<String,Object>> hashtableSQL(String strSql) {

        ResultSet rs = null;
        Hashtable<String,Hashtable<String,Object>> lhtResultSet=null;


        //連結資料庫
        DBBean db = ConnectionDB(getConnection());
        //-------------------
        if (getDBDebugMode()) {
            System.out.println(getProgName() +
                               " (hashtableSQL) --> Start time =" +
                               PublicFunction.getSYSDATE("yyyy/MM/dd HH:mm:ss"));
            System.out.println(getProgName() + " (hashtableSQL) --> strSql=" +
                               strSql);
        }

        try {
            rs = db.querySQL(strSql);

            //modify 20080718 by GM because 原本會先將ResultSet 轉成 Hashtable再欄位名稱放入garlColumnLabel，但因在轉
            //Hashtable時ResultSet loop完後被自動被release(Sqlite)，造成再執行getResultSetColumn時，無法取得MetaData
            //，故改變做法，先取得欄位名稱後再將ResultSet轉成Hashtable
            
            //將欄位名稱轉成一個ArrayList
            garlColumnLabel = DBFunction.getResultSetColumn(rs.getMetaData(),gblnDBDebug,"QueryBean.java (hashtableSQL)");
            
            //將ResultSet 轉成 Hashtable
            lhtResultSet = DBFunction.transferResultSetToHashtable(rs,rs.getMetaData(),gblnDBDebug,"QueryBean.java (hashtableSQL)");
            
            //取得資料筆數
            setRowcount((lhtResultSet==null ? 0 : lhtResultSet.size()));

        } catch (Exception ex) {
            setErrMsg(getProgName() + " (hashtableSQL) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() +
                               " (hashtableSQL,parameter:strSql) --> SQL : " +
                               strSql);
            System.out.println(getProgName() +
                               " (hashtableSQL) --> Exception : " +
                               ex.toString());
        } finally {
            if (rs != null) {
                try {
                    if (!getDBType().toUpperCase().trim().equals("ACCESS")) {
                        rs.getStatement().close();
                        rs.close();
                    }
                } catch (Exception se) {
                    if(getDBDebugMode()){//因若使用connection pool resultset close 一定會有exception ，所以用debug 來決定要不要秀出這個exception
                        System.out.println(
                                "QueryBean(hashtableSQL) rs close eroor :" +
                                se.toString() + "\n");
                        se.printStackTrace();
                    }
                }
                rs = null;
            } //釋放沒用的ResultSet

            //----------------------------
            if (db != null) {
                db.close();
            }
            if (getDBDebugMode()) {
                System.out.println(getProgName() +
                                   " (hashtableSQL) --> end time =" +
                                   PublicFunction.getSYSDATE(
                        "yyyy/MM/dd HH:mm:ss"));
            }

        }
        return lhtResultSet;
    } //Hashtable hashtableSQL -- End

    /**
     * 執行Insert,Update,Delete SQL語法
     * @param strSql String SQL
     * @return boolean
     */
    public boolean executeSQL(String strSql) {
        //連結資料庫
        DBBean db = ConnectionDB(getConnection());
        //-------------------
        if (getDBDebugMode()) {
            System.out.println(getProgName() +
                               " (executeSQL,parameter:strSql) --> strSql=" +
                               strSql);
        }
        if (getDBDebugMode()) {
            System.out.println(getProgName() +
                               " (executeSQL,parameter:strSql) --> SourceCharSet=" +
                               getSourceCharset() + "  TargetCharSet=" +
                               getTargetCharset());
        }
        boolean blnSuccess = false;
        try {
            if (!strSql.trim().equals("")) {
                blnSuccess = db.executeSQL(convertSQLParams(strSql,
                        getSourceCharset(), getTargetCharset()));
            } else
            if (getDBDebugMode()) {
                System.out.println(getProgName() +
                                   " (executeSQL,parameter:strSql) --> 您傳入的 SQL 為空白,所以無法執行!!");
            }
            if (getDBDebugMode()) {
                System.out.println(getProgName() +
                                   " (executeSQL,parameter:strSql) --> Execute Success==" +
                                   blnSuccess);
            }
        } catch (Exception ex) {
            setErrMsg(getProgName() +
                      " (executeSQL,parameter:strSql) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() +
                               " (executeSQL,parameter:strSql) --> SQL : " +
                               strSql);
            System.out.println(getProgName() +
                               " (executeSQL,parameter:strSql) --> Exception : " +
                               ex.toString());
            blnSuccess = false;
        } finally {
            if (getDBDebugMode() && getUseConnPool()) {
                System.out.println("*** Connection pool Close! ***");
            }
            if (getDBDebugMode() && (getUseConnPool() == false)) {
                System.out.println("*** JDBC Close! ***");
            }
            if (db != null) {
                db.close();
            }

        }
        return blnSuccess;
    } //executeSQL -- End

    /**
     * 執行Insert,Update,Delete SQL語法(適用特殊型態的欄位)
     * @param strSql String
     * @param vtrParams Vector 如需update 日期欄位，先將日期轉為Timestamp再放入Vector
     * @return boolean
     * @sample String lstrSQL="update wkf_jst_eng set rec_date_time=?";<br>
     * Vector lvecPara=new Vector();<br>
     * lvecPara.add(PublicCheck.getgetTimestempDate("2005/12/31"));<br>
     * (new QueryBean()).executeSQL(lstrSQL,lvecPara);
     */
    public boolean executeSQL(String strSql, Vector<Object> vtrParams) {
        //連結資料庫
        DBBean db = ConnectionDB(getConnection());
        //-------------------
        if (getDBDebugMode()) {
            System.out.println(getProgName() +
                               " (executeSQL,parameter:strSql、vtrParams) --> strSql=" +
                               strSql);
        }
        if (getDBDebugMode()) {
            System.out.println(getProgName() +
                               " (executeSQL,parameter:strSql、vtrParams) --> SourceCharSet=" +
                               getSourceCharset() + "  TargetCharSet=" +
                               getTargetCharset());
        }
        boolean blnSuccess = false;
        int intStartIndex = 0;
        int intEndIndex = 0;

        try {
            for (int i = 0; i < vtrParams.size(); i++) {
                if (vtrParams.get(i) instanceof String) {
                    vtrParams.set(i,
                                  PublicFunction.getTransCode(vtrParams.get(i).
                            toString(),
                            getSourceCharset(), getTargetCharset()));
                }
                if (getDBDebugMode()) {
                    if (strSql.trim().toUpperCase().substring(0,
                            6).equals("INSERT")) { //新增的sql
                        intEndIndex = strSql.indexOf(",", intStartIndex);
                        if (i == 0) {
                            if (strSql.toUpperCase().indexOf("(") != -1) {
                                intStartIndex = strSql.toUpperCase().indexOf(
                                        "(") + 1;
                            }
                        }
                        if (i == (vtrParams.size() - 1)) {
                            if (strSql.toUpperCase().indexOf(")") != -1) {
                                intEndIndex = strSql.toUpperCase().indexOf(")");
                            }
                        }
                        System.out.println("*** vtrParams" + i + " in " +
                                           strSql.substring(intStartIndex,
                                intEndIndex) + " ==> " + vtrParams.get(i));
                        intStartIndex = intEndIndex + 1;
                    } else if (strSql.trim().toUpperCase().substring(0, 6).
                               equals("UPDATE")) { //修改的sql
                        intEndIndex = strSql.indexOf("=", intStartIndex);
                        if (i == 0) {
                            if (strSql.toUpperCase().indexOf(" SET ") != -1) {
                                intStartIndex = strSql.toUpperCase().indexOf(
                                        " SET ") + 4;
                            }
                        }
                        System.out.println("*** vtrParams" + i + " in " +
                                           strSql.substring(intStartIndex,
                                intEndIndex) + " ==> " + vtrParams.get(i));
                        intStartIndex = intEndIndex + 3;
                    }
                }
            }
            if (!strSql.trim().equals("")) {
                blnSuccess = db.executeSQL(strSql, vtrParams);
            } else
            if (getDBDebugMode()) {
                System.out.println(getProgName() +
                                   " (executeSQL,parameter:strSql、vtrParams) --> 您傳入的 SQL 為空白,所以無法執行!!");
            }
            if (getDBDebugMode()) {
                System.out.println(getProgName() +
                                   " (executeSQL,parameter:strSql、vtrParams) --> Execute Success=" +
                                   blnSuccess);
            }
        } catch (Exception ex) {
            setErrMsg(getProgName() +
                      " (executeSQL,parameter:strSql、vtrParams) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() +
                               " (executeSQL,parameter:strSql) --> SQL : " +
                               strSql);
            System.out.println(getProgName() +
                               " (executeSQL,parameter:strSql、vtrParams) --> Exception : " +
                               ex.toString());
            blnSuccess = false;
        } finally {
            if (db != null) {
                db.close();
            }

        }
        return blnSuccess;
    } //executeSQL -- End

    /**
     * 執行一組連續的SQL語法,要全部成功才會做commit,否則會rollback(Transaction)
     * @param arySql ArrayList 陣列SQL
     * @return String 錯誤訊息(String) , 若無錯誤則回傳空白
     */
    public String executeSQL(ArrayList<String> arySql,ArrayList<String> aryParam) throws Exception{
        String strReturn = "", strSql = "";
        boolean blnSuccess = false;
        boolean blnAutoCommitMode = true;
        int kk = 0;

        //連結資料庫
        DBBean db = ConnectionDB(getConnection());
        //-------------------
        try {
            if (arySql != null && aryParam != null) {
                if(arySql.size()!=aryParam.size()){
                    strReturn = "Error : Sql ArrayList size not equals Param ArrayList size";
                    System.out.println(getProgName() +
                                       " (executeSQL,parameter:ArrayList Sql) --> " + strReturn);
                    throw new Exception(strReturn);
                }
                blnAutoCommitMode = getAutoCommitMode();
                setAutoCommitMode(false);
                for (int i = 0; i < arySql.size(); i++) {
                    kk = i;
                    strSql = arySql.get(i).toString();
                    if (getDBDebugMode()) {
                        System.out.println(getProgName() +
                                           " (executeSQL,parameter:ArrayList Sql) --> strSql[" +
                                           i + "]=" + strSql);
                    }
                    if (getDBDebugMode()) {
                        System.out.println(getProgName() +
                                           " (executeSQL,parameter:ArrayList Sql) --> SourceCharSet=" +
                                           getSourceCharset() +
                                           "  TargetCharSet=" +
                                           getTargetCharset());
                    }
                    blnSuccess = db.executeSQL(convertSQLParams(strSql,
                            getSourceCharset(), getTargetCharset()));
                    if (blnSuccess == false) {
                        strReturn =
                                "ExecuteSQL Error (SQL Statement had rollBack) ---- Error Sql[" +
                                kk + "]=" + strSql;
                        db.rollback();
                        break;
                    }
                    if (getDBDebugMode()) {
                        System.out.println(getProgName() +
                                           " (executeSQL,parameter:ArrayList Sql) --> Execute Success==" +
                                           blnSuccess);
                    }
                }
                if (blnSuccess == true) {
                    db.commit();
                }
            } else {
                strReturn = "Error : ArrayList Sql is null";
                System.out.println(getProgName() +
                                   " (executeSQL,parameter:ArrayList Sql) --> Error : ArrayList Sql is null");
                throw new Exception(strReturn);
            }
        } catch (Exception ex) {
            strReturn =
                    "ExecuteSQL Error (SQL Statement had rollBack) ---- Error Sql[" +
                    kk + "]=" + strSql + "\nException : " + ex.toString();
            System.out.println(getProgName() +
                               " (executeSQL,parameter:strSql) --> SQL : " +
                               strSql);
            System.out.println(getProgName() +
                               " (executeSQL,parameter:ArrayList Sql) --> Sql[" +
                               kk + "]=" + strSql + "\nException : " +
                               ex.toString());
            blnSuccess = false;
        } finally {
            if (db != null) {
                db.close();
            }
            setAutoCommitMode(blnAutoCommitMode);

        }
        return strReturn;
    } //executeSQL -- End

    /**
     * 執行一組連續的SQL語法,要全部成功才會做commit,否則會rollback
     * @param arySql ArrayList 陣列SQL
     * @return String 錯誤訊息(String) , 若無錯誤則回傳空白
     */
    public String executeSQL(ArrayList<String> arySql) {
        String strReturn = "", strSql = "";
        boolean blnSuccess = false;
        boolean blnAutoCommitMode = true;
        int kk = 0;

        //連結資料庫
        DBBean db = ConnectionDB(getConnection());
        //-------------------
        try {
            if (arySql != null) {
                blnAutoCommitMode = getAutoCommitMode();
                setAutoCommitMode(false);
                for (int i = 0; i < arySql.size(); i++) {
                    kk = i;
                    strSql = arySql.get(i).toString();
                    if (getDBDebugMode()) {
                        System.out.println(getProgName() +
                                           " (executeSQL,parameter:ArrayList Sql) --> strSql[" +
                                           i + "]=" + strSql);
                    }
                    if (getDBDebugMode()) {
                        System.out.println(getProgName() +
                                           " (executeSQL,parameter:ArrayList Sql) --> SourceCharSet=" +
                                           getSourceCharset() +
                                           "  TargetCharSet=" +
                                           getTargetCharset());
                    }
                    blnSuccess = db.executeSQL(convertSQLParams(strSql,
                            getSourceCharset(), getTargetCharset()));
                    if (blnSuccess == false) {
                        strReturn =
                                "ExecuteSQL Error (SQL Statement had rollBack) ---- Error Sql[" +
                                kk + "]=" + strSql;
                        db.rollback();
                        break;
                    }
                    if (getDBDebugMode()) {
                        System.out.println(getProgName() +
                                           " (executeSQL,parameter:ArrayList Sql) --> Execute Success==" +
                                           blnSuccess);
                    }
                }
                if (blnSuccess == true) {
                    db.commit();
                }
            } else {
                strReturn = "Error : ArrayList Sql is null";
                System.out.println(getProgName() +
                                   " (executeSQL,parameter:ArrayList Sql) --> Error : ArrayList Sql is null");
            }
        } catch (Exception ex) {
            strReturn =
                    "ExecuteSQL Error (SQL Statement had rollBack) ---- Error Sql[" +
                    kk + "]=" + strSql + "\nException : " + ex.toString();
            System.out.println(getProgName() +
                               " (executeSQL,parameter:strSql) --> SQL : " +
                               strSql);
            System.out.println(getProgName() +
                               " (executeSQL,parameter:ArrayList Sql) --> Sql[" +
                               kk + "]=" + strSql + "\nException : " +
                               ex.toString());
            blnSuccess = false;
        } finally {
            if (db != null) {
                db.close();
            }
            setAutoCommitMode(blnAutoCommitMode);

        }
        return strReturn;
    } //executeSQL -- End

    /*Purpose:將SQL where 條件式中的值若為文字格式轉換成指定的CharSet
      Command:strSQL ------------ 要進行轉換的SQL statement
              strSourceCharset -- 轉換用的中介編碼(Tomcat為通用碼ISO-8859-1)
              strSourceCharset -- 欲轉換成何種編碼
      Return:轉換後的SQL statment */
    private String convertSQLParams(String strSQL, String strSourceCharset,
                                    String strTargetCharset) {
        try {
            StringBuffer strResult = new StringBuffer("");

            strResult.append(PublicFunction.getTransCode(strSQL,
                    strSourceCharset,
                    strTargetCharset));
            return strResult.toString();
        } catch (Exception e) {
            setErrMsg(getProgName() + " (ConverSQLParams) --> Exception : " +
                      e.toString());
            System.out.println(getProgName() +
                               " (ConverSQLParams) --> Exception : " +
                               e.toString());
            return "convert error";
        }
    }

    /**
     * 取得SQL執行完後的ResultSet 筆數
     * @param strSql String
     * @return int
     */
    public int rowCountSQL(String strSql) {
        int intRowCount = 0; //回傳用的總筆數
        Statement stmt = null;
        ResultSet rs = null;
        //連結資料庫
        DBBean db = ConnectionDB(getConnection());
        //-------------------
        if (getDBDebugMode()) {
            System.out.println(getProgName() + " (rowCountSQL) --> strSql=" +
                               strSql);
        }
        try {
            if (strSql.trim().length() > 0) {
                stmt = getConnection().createStatement(ResultSet.
                        TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs = stmt.executeQuery(strSql);
                if (rs.last()) {
                    intRowCount = rs.getRow(); //取得總筆數
                }
            }
        } catch (Exception e) {
            setErrMsg(getProgName() + " (rowCountSQL) --> Exception : " +
                      e.toString());
            System.out.println(getProgName() +
                               " (rowCountSQL,parameter:strSql) --> SQL : " +
                               strSql);
            System.out.println(getProgName() +
                               " (rowCountSQL) --> Exception : " +
                               e.toString());
        } finally {
            if (getDBDebugMode()) {
                System.out.println(getProgName() + " (rowCountSQL) --> 總筆數 = " +
                                   intRowCount);
            }

            try {
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (db != null) {
                    db.close();
                    db = null;
                }
            } catch (Exception e) {}

        }
        return intRowCount;
    } //End - rowCountSQL

    /*private boolean setBundleValue(ResourceBundle prblProperties) {
        return configurationDBValue(new PropertyBean(prblProperties));
    }*/

    /*private boolean setBundleValue() {
        return configurationDBValue(new PropertyBean("DB_Config"));
    }*/

    /*private boolean setBundleValue(PropertyBean pptyBean) {
        return configurationDBValue(pptyBean);
    }*/

    private boolean setBundleValue(String pstrDBAlias) {
        return configurationDBValue(pstrDBAlias, new PropertyBean("DB_Config"));
    }

    /**
     * configurationDBValue
     *
     * @param pstrDBAlias String
     * @param pblnDBDebug boolean
     * @param objRB PropertyBean
     * @return boolean
     */
    private boolean configurationDBValue(String pstrDBAlias,
                                         PropertyBean objRB) {
        try {

        	if(pstrDBAlias.toUpperCase().indexOf("SQLITE:")==0){
                    setDBType("SQLITE");
                    setDBSid("NONE");
                    setDBHost(pstrDBAlias.substring(pstrDBAlias.toUpperCase().indexOf("SQLITE:") + 7));
                    setDBPort("NONE");
                    setDBUser("NONE");
                    setDBPasswords("NONE");
        	}
        	else{
                    setDBType(getConfigurationDBValue(objRB, pstrDBAlias + "_DB"));
                    setDBHost(getConfigurationDBValue(objRB, pstrDBAlias + "_DBHost"));
                    setDBSid(getConfigurationDBValue(objRB, pstrDBAlias + "_DBSid"));
                    setDBPort(getConfigurationDBValue(objRB, pstrDBAlias + "_DBPort"));
                    setDBUser(getConfigurationDBValue(objRB, pstrDBAlias + "_DBUser"));
                    setDBPasswords(PublicEncryption.getPubDeEncrptionString(
                    getConfigurationDBValue(objRB, pstrDBAlias + "_DBPwd")));
        	}
            
            setDBDriver(getConfigurationDBValue(objRB,
                                                getDBType().toUpperCase() + "_DRIVER"));
            setDBPrefix(getConfigurationDBValue(objRB,
                                                getDBType().toUpperCase() + "_PREFIX"));

            setEncryption(getConfigurationDBValue(objRB, "ENCRYPTION"));

            return true;

        } catch (Exception ex) {
            setErrMsg(getProgName() +
                      " (configurationDBValue(pstrDBAlias,pblnDBDebug,objRB)) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() +
                               " (configurationDBValue(pstrDBAlias,pblnDBDebug,objRB)) --> Exception : " +
                               ex.toString());
            if(getDBDebugMode()){
                ex.printStackTrace();
            }
            return false;
        }
    }

    /*private boolean configurationDBValue(PropertyBean objRB) {
        try {
            setDBDebugMode(objRB.getBoolean("DEBUG"));

            setActiveSite(objRB.getString("ACTIVE_SITE"));
            if (getActiveSite() == null) {
                throw new Exception(
                        "ACTIVE_SITE key is null value in DB_Config ");
            }

            setDBDriver(objRB.getString(getActiveSite() + "_DB_DRIVER"));
            if (getDBDriver() == null) {
                throw new Exception("DB_DRIVER key is null value in DB_Config ");
            }

            setDBHost(objRB.getString(getActiveSite() + "_DBHost"));
            if (getDBHost() == null) {
                throw new Exception(getActiveSite() +
                                    "_DBHost key is null value in DB_Config ");
            }

            setDBPasswords(objRB.getString(getActiveSite() + "_DBPwd"));
            if (getDBPasswords() == null) {
                throw new Exception(getActiveSite() +
                                    "_DBPwd key is null value in DB_Config ");
            }

            setDBPort(objRB.getString(getActiveSite() + "_DBPort"));
            if (getDBPort() == null) {
                throw new Exception(getActiveSite() +
                                    "_DBPort key is null value in DB_Config ");
            }

            setDBPrefix(objRB.getString(objRB.getString("ACTIVE_SITE") +
                                        "_DB_PREFIX"));
            if (getDBPrefix() == null) {
                throw new Exception("DB_PREFIX key is null value in DB_Config ");
            }

            setDBSid(objRB.getString(getActiveSite() + "_DBSid"));
            if (getDBSid() == null) {
                throw new Exception(getActiveSite() +
                                    "_DBSid key is null value in DB_Config ");
            }

            setDBType(objRB.getString(getActiveSite() + "_DB"));
            if (getDBType() == null) {
                throw new Exception(getActiveSite() +
                                    "_DB key is null value in DB_Config ");
            }

            setDBUser(objRB.getString(getActiveSite() + "_DBUser"));
            if (getDBUser() == null) {
                throw new Exception(getActiveSite() +
                                    "_DBUser key is null value in DB_Config ");
            }

            setLookupName(objRB.getString("LOOKUP_NAME"));
            if (getLookupName() == null) {
                throw new Exception(
                        "LOOKUP_NAME key is null value in DB_Config ");
            }

            setSourceCharset(objRB.getString("SOURCE_CHARSET"));
            if (getSourceCharset() == null) {
                throw new Exception(
                        "SOURCE_CHARSET key is null value in DB_Config ");
            }

            setTargetCharset(objRB.getString("TARGET_CHARSET"));
            if (getTargetCharset() == null) {
                throw new Exception(
                        "TARGET_CHARSET key is null value in DB_Config ");
            }

            return true;
        } catch (Exception ex) {
            setErrMsg(getProgName() +
                      " (configurationDBValue) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() +
                               " (configurationDBValue) --> Exception : " +
                               ex.toString());
            return false;
        }
    }*/

    private String getConfigurationDBValue(PropertyBean ptyRB, String pstrKey) throws
            Exception {
        String lstrValue = null;

        try {
            lstrValue = ptyRB.getString(pstrKey);
            if (lstrValue == null || lstrValue.trim().equals("")) {
                throw new Exception(pstrKey + " value is null or empty!!");
            }
        } catch (Exception e) {
            throw new Exception(e.toString());
        }
        return lstrValue;
    }

    /**
     * 取得QueryBean中所產生的Connection
     * @return Connection
     */
    public Connection getConnection() {
        return this.gconn;
    }

    /**
     * 關閉Connection(務必要執行)
     * @throws Exception
     */
    public void close() throws Exception {

        if (this.gconn != null) {
        	if(!getAutoCommitMode()){//若沒有AutoCommit則在關閉Transaction前自動rollback
        		this.gconn.rollback();
        	}
            this.gconn.close();
        }

        if (getDBDebugMode() && getUseConnPool()) {
            System.out.println("*** Connection pool Close! ***");
        }
        if (getDBDebugMode() && (getUseConnPool() == false)) {
            System.out.println("*** JDBC Close! ***");
        }
    }

    /**
     * 直接傳入Connection
     * @param pconn Connection
     * @throws Exception
     */
    public void setConnection(Connection pconn) throws Exception {
        this.gconn = pconn;
    }

    /**
     * 取得DB_Config中Debug所設定的值
     * @return boolean
     */
    public boolean getDBDebugMode() {
        return this.gblnDBDebug;
    }

    private void setDBDebugMode(boolean pblnDebug) {
        this.gblnDBDebug = pblnDebug;
    }

    /**
     * 取得DB_Config中ActiveSite所設定的值
     * @return String
     */
    public String getActiveSite() {
        return this.gstrActiveSite;
    }

    /*private void setActiveSite(String pstrActiveSite) {
        this.gstrActiveSite = pstrActiveSite;
    }*/

    /**
     * 取得DB_Config中DB_Driver所設定的值
     * @return String
     */
    public String getDBDriver() {
        return this.gstrDBDriver;
    }

    private void setDBDriver(String pstrDBDriver) {
        this.gstrDBDriver = pstrDBDriver;
    }

    /**
     * 取得DB_Config中DB_Host所設定的值
     * @return String
     */
    public String getDBHost() {
        return this.gstrDBHost;
    }

    private void setDBHost(String pstrDBHost) {
        this.gstrDBHost = pstrDBHost;
    }

    private String getDBPasswords() {
        return this.gstrDBPasswords;
    }

    private void setDBPasswords(String pstrDBPasswords) {
        this.gstrDBPasswords = pstrDBPasswords;
    }

    /**
     * 取得DB_Config中DB_Port所設定的值
     * @return String
     */
    public String getDBPort() {
        return this.gstrDBPort;
    }

    private void setDBPort(String pstrDBPort) {
        this.gstrDBPort = pstrDBPort;
    }

    /**
     * 取得DB_Config中DB_Prefix所設定的值
     * @return String
     */
    public String getDBPrefix() {
        return this.gstrDBPrefix;
    }

    private void setDBPrefix(String pstrDBPrefix) {
        this.gstrDBPrefix = pstrDBPrefix;
    }

    /**
     * 取得DB_Config中DB_Sid所設定的值
     * @return String
     */
    public String getDBSid() {
        return this.gstrDBSid;
    }

    private void setDBSid(String pstrDBSid) {
        this.gstrDBSid = pstrDBSid;
    }

    /**
     * 取得DB_Config中DB_Type所設定的值
     * @return String
     */
    public String getDBType() {
        return this.gstrDBType;
    }

    private void setDBType(String pstrDBType) {
        this.gstrDBType = pstrDBType;
    }

    /**
     * 取得DB_Config中DB_User所設定的值
     * @return String
     */
    public String getDBUser() {
        return this.gstrDBUser;
    }

    private void setDBUser(String pstrDBUser) {
        this.gstrDBUser = pstrDBUser;
    }

    /**
     * 取得DB_Config中LookupName所設定的值
     * @return String
     */
    public String getLookupName() {
        return this.gstrLookupName;
    }

    private void setLookupName(String pstrLookupName) {
        this.gstrLookupName = pstrLookupName;
    }

    /**
     * 取得DB_Config中Source_Charset所設定的值
     * @return String
     */
    public String getSourceCharset() {
        return this.gstrSourceCharset;
    }

    private void setSourceCharset(String pstrSourceCharset) {
        this.gstrSourceCharset = pstrSourceCharset;
    }

    /**
     * 取得DB_Config中Target_Charset所設定的值
     * @return String
     */
    public String getTargetCharset() {
        return this.gstrTargetCharset;
    }

    private void setTargetCharset(String pstrTargetCharset) {
        this.gstrTargetCharset = pstrTargetCharset;
    }

    private void setErrMsg(String ErrMsg) {
        this.gstrErrMsg = (ErrMsg == null ? "" : ErrMsg.trim());
    }

    /**
     * 取得執行SQL 發生錯誤時的Error Message<br>
     * 每取完一次Error Message就會被清掉
     * @return String Error Message
     */
    public String getErrMsg() {
        String strErrMsg_Temp = gstrErrMsg;
        setErrMsg(""); //get完之後就將錯誤訊息清空
        return strErrMsg_Temp;
    }

    /**
     * 取得是否自動Commit
     * @return boolean
     */
    public boolean getAutoCommitMode() {
        try {
            return getConnection().getAutoCommit();
        } catch (Exception e) {
            setErrMsg(getProgName() + "(getAutoCommitMode) Exception:" +
                      e.toString());
            e.printStackTrace();
            return false;
        }
    }

    /**
     * User 可自行控制是否允許自動Commit
     * @param pblnAutoCommit boolean
     */
    public void setAutoCommitMode(boolean pblnAutoCommit) {
        try {
            getConnection().setAutoCommit(pblnAutoCommit);
        } catch (Exception e) {
            setErrMsg(getProgName() + "(setAutoCommitMode) Exception:" +
                      e.toString());
            e.printStackTrace();
        }
    }

    private void setProgName(String sProg) {
        this.gstrProgName = sProg;
    }

    /**
     * 取得此Class的名稱
     * @return String 回傳固定值 QueryBean.java
     */
    public String getProgName() { //取得該支程式的名稱 (QueryBean.java)
        return this.gstrProgName;
    }

    /**
     * 設定資料筆數
     * @param rowcount int
     * @deprecated 沒有在使用
     */
    public void setRowcount(int rowcount) {
        this.gintRowcount = rowcount;
    }

    /**
     * 取得資料筆數<br>
     * 透過 querySQL,hashtableSQL 取得資料錄後，可直接以此method取得資料筆數
     * @return int 資料錄的筆數
     */
    public int getRowcount() {
        return gintRowcount;
    }

    /**
     * 使用querySQL後，可透此method 取得查詢結果的各欄位名稱
     * @return ArrayList
     */
    public ArrayList<String> getArlcolumnLabel() {
        return garlColumnLabel;
    }

    private void setUseConnPool(boolean pblnUse) {
        this.gblnConPool = pblnUse;
    }

    /**
     * 是否是使用Connection poolling連線
     * @return boolean
     */
    public boolean getUseConnPool() {
        return this.gblnConPool;
    }

    public void setEncryption(String pstrEncryption) {
        this.gstrEncryption = pstrEncryption;
    }

    public String getEncryption() {
        return gstrEncryption;
    }

    public String getDBAlias() {
        return gstrDBAlias;
    }

    /**
     * 當回傳recordset只有單一筆的情況，可以使用，以省下多解一次Collection的麻煩
     *
     * @param pstrSQL SQL
     * @return Hashtable 回傳值類似hashtableSQL，但只有一維的欄位名稱
     */
    public Hashtable<String,Object> readSingleRowData(String pstrSQL) {
        Hashtable<String,Hashtable<String,Object>> lhtData=null;
        try {
            lhtData=hashtableSQL(pstrSQL);
            if(lhtData==null || lhtData.get("0")==null){return null;}
            return ((Hashtable<String,Object>) lhtData.get("0"));
        } catch (Exception ex) {
            setErrMsg(getProgName() + " (readSingleRowData) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() +
                               " (readSingleRowData,parameter:strSql) --> SQL : " +
                               pstrSQL);
            System.out.println(getProgName() +
                               " (readSingleRowData) --> Exception : " +
                               ex.toString());
            return null;
        }
        finally{
            if(lhtData!=null){
                lhtData.clear();
                lhtData=null;
            }
        }
    }

    /**
     * 當回傳recordset只有單一值的情況，可以使用，以省下解Collection的麻煩
     *
     * @param pstrSQL String
     * @return String 欄位值
     * @sample pstrSQL value is select count(*) from dual;
     */
    public String readSingleValueData(String pstrSQL) {
        ArrayList<ArrayList<Object>> laryData=null;
        try {
            laryData=querySQL(pstrSQL);
            if(laryData==null || laryData.get(0)==null || ((ArrayList<Object>) laryData.get(0)).get(0)==null){return null;}
            return ((ArrayList<Object>) laryData.get(0)).get(0).toString();
        } catch (Exception ex) {
            setErrMsg(getProgName() + " (readSingleValueData) --> Exception : " +
                      ex.toString());
            System.out.println(getProgName() +
                               " (readSingleValueData,parameter:strSql) --> SQL : " +
                               pstrSQL);
            System.out.println(getProgName() +
                               " (readSingleValueData) --> Exception : " +
                               ex.toString());
            return null;
        }
        finally{
            if(laryData!=null){
                laryData.clear();
                laryData = null;
            }
        }
    }

    public void setDBAlias(String gstrDBAlias) {
        this.gstrDBAlias = gstrDBAlias;
    }

    private String getDBLocation(String pstrDBType,String pstrDBHost, String pstrDBPort, String pstrDBSid) throws Exception{
        StringBuffer lstbDBLocal = null;

        try{
            lstbDBLocal = new StringBuffer();

            if(pstrDBType.equals("ORACLE")){

            }
            else if(pstrDBType.equals("ACCESS")){

            }
            else if(pstrDBType.equals("SQLITE")){

            }
            else if(pstrDBType.equals("MYSQL")){
                lstbDBLocal.append(pstrDBHost);

                if(!pstrDBPort.equals("") && !pstrDBPort.toUpperCase().equals("NONE")){
                    lstbDBLocal.append(":");
                    lstbDBLocal.append(pstrDBPort);
                }

                lstbDBLocal.append("/");

                lstbDBLocal.append(pstrDBSid);
            }

            return lstbDBLocal.toString();
        }
        catch(Exception e){
            throw new Exception(e.getMessage());
        }
//                    lstrDBLocal = getDBHost().toLowerCase().trim() +
//                          (getDBPort().trim().equals("") || getDBPort().trim().toUpperCase().equals("NONE") ? "" :
//                           ":" + getDBPort()) +
//                          (getDBSid().trim().equals("") || getDBSid().trim().toUpperCase().equals("NONE") ? "" :
//                           (getDBType().toUpperCase().equals("ACCESS") ? "" :
//                            ":") + getDBSid());
    }
}
